﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace BST250301TestStand.Models.Helpers
{
    public class DatabaseHelper
    {
        private string _connectionString;

        /// <summary>
        /// 初始化 MySQLHelper 类的新实例。
        /// </summary>
        /// <param name="connectionString">MySQL 数据库连接字符串。</param>
        public DatabaseHelper(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// 执行非查询SQL语句（如INSERT, UPDATE, DELETE）。
        /// </summary>
        /// <param name="query">要执行的SQL语句。</param>
        /// <param name="parameters">SQL参数。</param>
        /// <returns>受影响的行数。</returns>
        public int ExecuteNonQuery(string query, Dictionary<string, object> parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            command.Parameters.AddWithValue(param.Key, param.Value);
                        }
                    }
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行查询并返回第一行第一列的值。
        /// </summary>
        /// <param name="query">要执行的SQL查询。</param>
        /// <param name="parameters">SQL参数。</param>
        /// <returns>查询结果的第一行第一列的值。</returns>
        public object ExecuteScalar(string query, Dictionary<string, object> parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            command.Parameters.AddWithValue(param.Key, param.Value);
                        }
                    }
                    return command.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行查询并返回一个DataReader。
        /// </summary>
        /// <param name="query">要执行的SQL查询。</param>
        /// <param name="parameters">SQL参数。</param>
        /// <returns>MySqlDataReader 对象。</returns>
        public MySqlDataReader ExecuteReader(string query, Dictionary<string, object> parameters = null)
        {
            MySqlConnection connection = new MySqlConnection(_connectionString);
            connection.Open();
            MySqlCommand command = new MySqlCommand(query, connection);
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value);
                }
            }
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行查询并返回一个包含查询结果的DataTable。
        /// </summary>
        /// <param name="query">要执行的SQL查询。</param>
        /// <param name="parameters">SQL参数。</param>
        /// <returns>包含查询结果的DataTable。</returns>
        public DataTable ExecuteDataTable(string query, Dictionary<string, object> parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            command.Parameters.AddWithValue(param.Key, param.Value);
                        }
                    }
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                }
            }
        }
        /// 测试数据库连接
        /// </summary>
        /// <returns>是否连接成功</returns>
        public bool TestConnection()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    conn.Open();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        public bool CreatAreaTable(string areaname)
        {
            string createTableSql = @$"CREATE TABLE IF NOT EXISTS `park_config_area_{areaname}` (
  `id` int NOT NULL AUTO_INCREMENT,
  `gateid` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `gatename` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `gatesn` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `gatedirect` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT '0',
  `cameraip` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `areaid` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `areaname` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `areadesc` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `create_user_id` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `create_user_name` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT (now()),
  `update_user_id` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `update_user_name` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `update_time` datetime DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `gateid` (`gateid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
        ";
            try
            {
                MySqlHelper.ExecuteNonQuery(_connectionString, createTableSql);
                Console.WriteLine("表 Users 创建成功！");
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("创建表失败：" + ex.Message);
            }
            return false;
        }
    }
}
